import sqlite3
from pathlib import Path
from datetime import datetime

DATABASE_PATH = Path(__file__).parent / "articles.db"

def get_db():
    conn = sqlite3.connect(str(DATABASE_PATH))
    conn.row_factory = sqlite3.Row
    return conn

def create_tables():
    """创建数据库表"""
    conn = get_db()
    cursor = conn.cursor()
    
    # 创建 AI 模型表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS ai_models (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            provider TEXT NOT NULL,
            model_id TEXT NOT NULL,
            api_type TEXT NOT NULL,
            description TEXT,
            max_tokens INTEGER NOT NULL DEFAULT 4096,
            temperature_default REAL NOT NULL DEFAULT 0.7,
            api_key TEXT NOT NULL,
            api_endpoint TEXT,
            api_version TEXT,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TIMESTAMP NOT NULL,
            updated_at TIMESTAMP NOT NULL
        )
    """)
    
    # 创建提示词模板表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS prompt_templates (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            description TEXT,
            template TEXT NOT NULL,
            template_type TEXT NOT NULL,
            variables TEXT NOT NULL,  -- JSON 格式存储
            default_values TEXT NOT NULL,  -- JSON 格式存储
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TIMESTAMP NOT NULL,
            updated_at TIMESTAMP NOT NULL
        )
    """)
    
    # 创建设置表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS settings (
            id INTEGER PRIMARY KEY,
            data TEXT NOT NULL  -- JSON 格式存储所有设置
        )
    """)
    
    # 创建用户表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id TEXT PRIMARY KEY,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_login TIMESTAMP,
            status TEXT CHECK(status IN ('active', 'inactive', 'suspended')) DEFAULT 'active'
        )
    """)
    
    # 创建文章表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS articles (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT,
        status TEXT DEFAULT 'draft',
        template_id TEXT,
        "references" TEXT,  -- 使用引号括起 references
        reference_content TEXT,
        reference_style TEXT,
        length TEXT,
        style TEXT,
        user_id TEXT,
        created_at TEXT,
        updated_at TEXT,
        summary TEXT,
        view_count INTEGER DEFAULT 0
    )
    ''')
    
    # 创建文章参数表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS article_params (
            id TEXT PRIMARY KEY,
            article_id TEXT NOT NULL UNIQUE,
            topic TEXT,
            style TEXT CHECK(style IN ('professional', 'casual', 'storytelling', 'technical', 'educational')) DEFAULT 'professional',
            length TEXT CHECK(length IN ('short', 'medium', 'long')) DEFAULT 'medium',
            audience TEXT CHECK(audience IN ('general', 'professional', 'young')) DEFAULT 'general',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
        )
    """)
    
    # 创建参考链接表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS reference_urls (
            id TEXT PRIMARY KEY,
            article_id TEXT NOT NULL,
            url TEXT NOT NULL,
            title TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
        )
    """)
    
    # 创建标签表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS tags (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # 创建文章标签关联表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS article_tags (
            article_id TEXT NOT NULL,
            tag_id TEXT NOT NULL,
            PRIMARY KEY (article_id, tag_id),
            FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
            FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
        )
    """)
    
    # 创建文章版本历史表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS article_versions (
            id TEXT PRIMARY KEY,
            article_id TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            description TEXT,
            FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
        )
    """)
    
    # 创建索引
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_articles_user ON articles(user_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_article_tags ON article_tags(tag_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_ref_urls_article ON reference_urls(article_id)")
    
    conn.commit()
    conn.close()

def dict_factory(cursor, row):
    fields = [column[0] for column in cursor.description]
    return {key: value for key, value in zip(fields, row)}

def get_db_with_dict_factory():
    conn = sqlite3.connect(str(DATABASE_PATH))
    conn.row_factory = dict_factory
    return conn 